package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;

import model.Software;

public class SoftwareDAO extends PersistenciaDAO {

	
	public Collection<HashMap<String, Object>> buscarDadosSoftwareBrowse() throws SQLException {

		Collection<HashMap<String, Object>> result = new ArrayList<HashMap<String,Object>>();
		Statement sta = connection.createStatement();
		ResultSet elements = sta.executeQuery(
				" SELECT s.cd_software as codigo, "+
				"  		s.ds_software as descricao "+
				" FROM quiosque.software s "+
				" ORDER BY s.cd_software ");
		
		while (elements.next()) {
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put("codigo", elements.getInt("codigo"));
			map.put("descricao", elements.getString("descricao"));
			result.add(map);
		}
		return result;
	}
	
	public Collection<HashMap<String, Object>> buscarDadosSoftwareSOBrowse() throws SQLException {

		Collection<HashMap<String, Object>> result = new ArrayList<HashMap<String,Object>>();
		Statement sta = connection.createStatement();
		ResultSet elements = sta.executeQuery(
				" SELECT s.cd_software as codigo, "+
				"  		s.ds_software as descricao "+
				" FROM quiosque.software s "+
				" WHERE s.id_sistema_operacional is not null AND s.id_sistema_operacional = 1"+
				" ORDER BY s.cd_software ");
		
		while (elements.next()) {
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put("codigo", elements.getInt("codigo"));
			map.put("descricao", elements.getString("descricao"));
			result.add(map);
		}
		return result;
	}


	public Software buscarSoftwarePorId(int id) throws SQLException {
		Software result = null;
		Statement sta = connection.createStatement();
		ResultSet elements = sta
				.executeQuery("SELECT * FROM quiosque.software WHERE cd_software = " + id);

		while (elements.next()) {
			result = montarSoftware(elements);
		}
		return result;
	}

	public Software buscarSoftwarePorDescricao(String descricao) throws SQLException {
		Software result = null;
		Statement sta = connection.createStatement();
		ResultSet elements = sta
				.executeQuery("SELECT * FROM quiosque.software WHERE ds_software = '" + descricao + "'");

		while (elements.next()) {
			result = montarSoftware(elements);
		}
		return result;
	}

	
	public Software montarSoftware(ResultSet elements) throws SQLException {
		Software soft = new Software();
		soft.setId(elements.getInt("cd_software"));
		soft.setDescricao(elements.getString("ds_software"));
		soft.setVersao(elements.getString("ds_versao"));
		soft.setSistemaOperacional(elements.getBoolean("id_sistema_operacional"));
		return soft;
	}
	
	public Collection<Software> buscarSoftwarePorComputador(int idComputador) throws SQLException{
		Collection<Software> softwares = new ArrayList<Software>();

		Statement sta = connection.createStatement();
		ResultSet elements = sta
		.executeQuery("SELECT * FROM computadorsoftware WHERE cd_computador = "	+ idComputador);

		while (elements.next()) {
			Software software = buscarSoftwarePorId(elements.getInt("cd_software"));
			softwares.add(software);
		}

		return softwares;
	}

	public Collection<Software> buscarTodos() throws SQLException {
		Collection<Software> result = new ArrayList<Software>();
		Statement sta = connection.createStatement();
		ResultSet elements = sta
				.executeQuery("SELECT * FROM quiosque.software s " +
						" WHERE s.id_sistema_operacional is null OR s.id_sistema_operacional = 0");

		while (elements.next()) {
			Software soft = montarSoftware(elements);
			result.add(soft);
		}
		return result;
	}


}
